package servlet.admin;

import java.io.File;
import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import javabean.Base;
import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;
 

@WebServlet("/admin/importExcelServlet")
public class ImportExcelServlet extends HttpServlet {
    private static final long serialVersionUID = 1L;
 
    protected void doPost(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        // 获取上传的 Excel 文件
        String filePath = "D:/data.xls";
        File file = new File(filePath);
 
        // 读取 Excel 文件中的数据
        Workbook workbook = null;
		try {
			workbook = Workbook.getWorkbook(file);
		} catch (BiffException | IOException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
	
		
        Sheet sheet = workbook.getSheet(0);
        int rows = sheet.getRows();
        int clos=sheet.getColumns();
        if(clos>7||clos<7) {
        	workbook.close();
        	response.getWriter().write("数据格式不正确，导入失败！");
        }
        
     // 准备数据
     		Connection connection = null;
     		PreparedStatement pstmt = null;
     		ResultSet resultSet = null;
     		int result = 0;
     		String sql = "";
     		int count = 0 ;
     		sql = "insert into books(name, author, library_id, sort_id, position, status, description) values(?,?,?,?,?,?,?)";
        
        for (int i = 0; i < rows; i++) {
            Cell[] cells = sheet.getRow(i);
            String name = cells[0].getContents();
            String author=cells[1].getContents();
            int  library_id= Integer.parseInt(cells[2].getContents());
            int  sort_id= Integer.parseInt(cells[3].getContents());
            String position = cells[4].getContents();
            int  status= Integer.parseInt(cells[5].getContents());
            String descrip = cells[6].getContents();
            
            
            try {
    			connection = (Connection) Base.getConnection();
    			pstmt = connection.prepareStatement(sql);
    			pstmt.setString(1, name);
    			pstmt.setString(2, author);
    			pstmt.setLong(3, library_id);
    			pstmt.setLong(4, sort_id);
    			pstmt.setString(5, position);
    			pstmt.setLong(6, status);
    			pstmt.setString(7, descrip);
    			result = pstmt.executeUpdate();
    			if(result==1) {
    				
    			}else {
    				response.getWriter().write("数据导入失败！");
    			}
    		} catch (SQLException e) {
    			
    		} catch (ClassNotFoundException e) {
    			e.printStackTrace();
    		}finally {
    			try {
    				Base.closeResource(connection, pstmt, null);
    			} catch (SQLException e) {
    				e.printStackTrace();
    			}
    		}
            
    
        }
        workbook.close();
 
        response.setContentType("text/html;charset=UTF-8");
        response.getWriter().write("数据导入成功！");
    }
}